﻿using Freshbox2._0.BLL;
using Freshbox2._0.MODEL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace Freshbox2._0.UI.Controllers
{
    public class CustomerFlowController : BaseController<usersorder>
    {
        //
        // GET: /CustomerFlow/
        //客户复购情况分析

        public override BLL.BaseBll<usersorder> bll
        {
            get
            {
                return new UsersOrderBll();
            }
        }

        UserBll userBll = new UserBll();
        MemberBll memberBll = new MemberBll();

        public class ListFlow
        {
            public int? userCode { get; set; }
            public string wechatName { get; set; }
            public string userPhone { get; set; }
            public int? visitorRate { get; set; }//访问频率
            public decimal? consumeMoney { get; set; }   //消费总金额
            public int? memberExp { get; set; }   //积分memberExp  
            public decimal? memberBalance { get; set; }  //余额memberBalance

        }
        //   orderTime    orderSumprice  orderPay  discountMoney  storeName
        public class oneOrder
        {
            public string orderCode { get; set; }
            public string orderTime { get; set; }
            public string storeName { get; set; }
            public decimal? discountMoney { get; set; }
            public decimal? orderSumprice { get; set; }
            public string orderType { get; set; }
        }

        /// <summary>
        /// 订单详细信息
        /// </summary>
        public class ListOrderDetail
        {
            public string orderCode;
            public string waresCode;
            public string waresName;
            public int waresNumber;
            public decimal waresPrice;
            public decimal TotalPrice;

        }

        OrderDetailsBll orderDetailBll = new OrderDetailsBll();

        WaresBll waresBll = new WaresBll();

        CouponBll couponBll = new CouponBll();

        public ActionResult GetFlow(string date1, string date2, string sort, string order)
        {
            var flowlist1 = new List<ListFlow>();
            // 以后记住啊  多条件的情况下  用类传递参数     
            var flowlist = new List<ListFlow>();
            //1.先查出  本月的交易成功的订单信息
            Expression<Func<usersorder, bool>> condition = x => x.orderStatus == 1 && x.userCode != 0;
            Expression<Func<user, bool>> condition2 = x => true;
            //如果为空 所有的时间 搜索条件  就默认查询今天的记录
            if (string.IsNullOrEmpty(date1) || string.IsNullOrEmpty(date2))
            {
                //时间类型相关的数据 转化为 string 类型的数据   以后 不同的计算机转化结果不同 ，这里不建议使用string 来比较时间相关的数据
                //DateTime todayNow = DateTime.Now;
                //DateTime midnight = DateTime.Now;
                //midnight = midnight.Date;
                //condition = x => x.orderStatus == 1 && x.orderTime <= todayNow && x.orderTime >= midnight &&x.userCode != 0;
                condition = x => x.orderStatus == 1;
            }
            //如果日期  不为空
            if ((date1 != "" && date1 != null) && (date2 != "" && date2 != null))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2;
            }
            var orderList2 = bll.Search(condition).GroupBy(x => x.userCode).Select(x => new { x.FirstOrDefault().userCode, visitorRate = x.Count(), consumeMoney = x.Sum(y => y.orderSumprice) }).ToList();


            if (orderList2.Count != 0)
            {
                foreach (var item in orderList2)
                {
                    var userInfoList = userBll.Search(x => x.userCode == item.userCode).Select(x => new { x.wechatName, x.wechatCode, x.userPhone, x.userName, userSex = x.userSex == 1 ? "男" : x.userSex == 2 ? "女" : "未知", x.userCode }).ToList();
                    if (userInfoList.Count != 0)
                    {
                        string weChartCode = userInfoList.FirstOrDefault().wechatCode;
                        var listMember = memberBll.Search(x => x.wechatCode == weChartCode).Select(x => new { x.wechatCode, x.memberExp, couponId = x.Id, x.memberBalance }).ToList();
                        ListFlow flowModel = new ListFlow();
                        flowModel.userCode = userInfoList.FirstOrDefault().userCode;

                        flowModel.wechatName = userInfoList.FirstOrDefault().wechatName;

                        flowModel.userPhone = userInfoList.FirstOrDefault().userPhone;
                        flowModel.visitorRate = item.visitorRate;
                        flowModel.consumeMoney = item.consumeMoney;
                        if (listMember.Count() != 0)
                        {
                            flowModel.memberBalance = listMember.FirstOrDefault().memberBalance;
                            flowModel.memberExp = listMember.FirstOrDefault().memberExp;
                        }
                        else
                        {
                            flowModel.memberBalance = 0;
                            flowModel.memberExp = 0;
                        }


                        flowlist.Add(flowModel);
                    }
                    else
                    {
                        ListFlow flowModel = new ListFlow();
                        flowModel.userCode = 0;
                        flowModel.wechatName = "未知";
                        flowModel.userPhone = "未知";
                        flowModel.visitorRate = item.visitorRate;
                        flowModel.consumeMoney = item.consumeMoney;
                        flowModel.memberBalance = 0;
                        flowModel.memberExp = 0;
                        flowlist.Add(flowModel);
                    }
                }
            }
            if (sort == "consumeMoney" && order == "desc")
            {

                flowlist1 = flowlist.OrderByDescending(x => x.consumeMoney).ToList();
            }
            if (sort == "consumeMoney" && order == "asc")
            {

                flowlist1 = flowlist.OrderBy(x => x.consumeMoney).ToList();
            }
            if (sort == "visitorRate" && order == "desc")
            {

                flowlist1 = flowlist.OrderByDescending(x => x.visitorRate).ToList();
            }
            if (sort == "visitorRate" && order == "asc")
            {

                flowlist1 = flowlist.OrderBy(x => x.visitorRate).ToList();
            }
            return Json(new { rows = flowlist1 }, JsonRequestBehavior.AllowGet);
        }

        StoreBll storeBll = new StoreBll();

        public ActionResult GetOneOrderByUser(string userCode, string date1, string date2)
        {
            int code ;
            if (userCode!="null"&&userCode!="")
            {
               code = Convert.ToInt32(userCode);
            }
            else
            {
               code = 0;
            }
            var oneOrderList = new List<oneOrder>();
            Expression<Func<usersorder, bool>> condition = x => x.orderStatus == 1 && x.userCode != 0 && x.userCode == code;
            if (string.IsNullOrEmpty(date1) || string.IsNullOrEmpty(date2))
            {
                condition = x => x.orderStatus == 1 && x.userCode == code && x.orderTime.Month==DateTime.Now.Month;
            }
            //如果日期  不为空
            if ((date1 != "" && date1 != null) && (date2 != "" && date2 != null))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2 && x.userCode == code;
            }
            var list = bll.Search(condition).Select(x => new { x.storeid, x.orderCode, x.orderTime, x.orderSumprice, x.discountMoney, orderPay = x.orderPay == 1 ? "支付宝" : x.orderPay == 2 ? "微信" : x.orderPay == 3 ? "优惠券" : "人脸", });
            foreach (var item in list)
            {
                string storeName = storeBll.Search(x => x.storeid == item.storeid).Select(x => new { x.storeName }).ToList().FirstOrDefault().storeName;
                oneOrder oneModel = new oneOrder();
                oneModel.discountMoney = item.discountMoney;
                oneModel.orderType = item.orderPay;
                oneModel.orderSumprice = item.orderSumprice;
                oneModel.storeName = storeName;
                oneModel.orderCode = item.orderCode;
                oneModel.orderTime = item.orderTime.ToString("yyyy-MM-dd HH:mm:ss"); //ToShortTimeString()  ;//ToLongDateString ToLongTimeString
                oneOrderList.Add(oneModel);
            }
            return Json(new { rows = oneOrderList }, JsonRequestBehavior.AllowGet);
        }

        WaresBll wareBll = new WaresBll();

        OrderDetailsBll orderDetailsBll = new OrderDetailsBll();

        //查询订单详细信息  查看每条订单对应的详细信息  

        public ActionResult GetDetailByID(string orderCode, string date1, string date2)
        {

            var list = orderDetailsBll.Search(x => x.orderCode == orderCode).ToList().Select(x => new { x.orderCode, x.waresCode, x.waresQuantity }).ToList();
            var listOrderDetail = new List<ListOrderDetail>();
            foreach (var oneDetailCode in list)
            {
                //查询是否有优惠券
                ListOrderDetail detail = new ListOrderDetail();
                var listCoupon = couponBll.Search(x => x.couponId == oneDetailCode.waresCode).Select(x => new { x.couponId, x.couponValue, }).ToList();
                if (listCoupon.Count != 0)
                {
                    detail.orderCode = oneDetailCode.orderCode;
                    detail.waresCode = oneDetailCode.waresCode;
                    detail.waresName = "优惠券";
                    detail.waresNumber = 1;
                    detail.waresPrice = Convert.ToDecimal(listCoupon.FirstOrDefault().couponValue);
                    detail.TotalPrice = Convert.ToDecimal(listCoupon.FirstOrDefault().couponValue * 1);//总价
                    listOrderDetail.Add(detail);
                }
                var list2 = wareBll.Search(x => x.waresCode == oneDetailCode.waresCode).Select(x => new { x.waresCode, x.waresName, x.waresPrice }).ToList();
                if (list2.Count != 0)
                {
                    detail.orderCode = oneDetailCode.orderCode;
                    detail.waresCode = oneDetailCode.waresCode;
                    detail.waresName = list2.FirstOrDefault().waresName;
                    detail.waresNumber = Convert.ToInt32(oneDetailCode.waresQuantity);
                    detail.waresPrice = Convert.ToDecimal(list2.FirstOrDefault().waresPrice);
                    detail.TotalPrice = Convert.ToDecimal(list2.FirstOrDefault().waresPrice * oneDetailCode.waresQuantity);
                    listOrderDetail.Add(detail);
                }
            }
            return Json(listOrderDetail);
        }


        //统计总的频次

        public int AllSum;  // 访问总频次

        public double RepeatRate; //复购率

        public ActionResult GetRate(string date1, string date2)
        {
            var flowlist1 = new List<ListFlow>();
            // 以后记住啊  多条件的情况下  用类传递参数     
            var flowlist = new List<ListFlow>();
            //1.先查出  交易成功的订单信息
            Expression<Func<usersorder, bool>> condition = x => x.orderStatus == 1;
            Expression<Func<user, bool>> condition2 = x => true;
            //如果为空 所有的时间 搜索条件  就默认查询所有的记录
            if (string.IsNullOrEmpty(date1) || string.IsNullOrEmpty(date2))
            {
                condition = x => x.orderStatus == 1;
            }
            //如果日期  不为空
            if ((date1 != "" && date1 != null) && (date2 != "" && date2 != null))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2;
            }
            //总的订单量   也就总的访问频次
            var orderList = bll.Search(condition).ToList();
            int a = orderList.Count();
            //根据用户进行分组  除支付宝支付的    
            var orderList2 = bll.Search(condition).Where(x => x.userCode != 0).GroupBy(x => x.userCode).ToList();
            //复购量
            int repeatNum = orderList.Count() - orderList2.Count();

            if (orderList.Count() != 0)
            {
                RepeatRate = Convert.ToDouble(Convert.ToDouble(repeatNum) / Convert.ToDouble(orderList.Count()));
            }
            else
            {
                RepeatRate = 0;
            }//(num/100).ToString("p");
            return Json(new { rows = flowlist1, AllSum = orderList.Count(), RepeatRate = (RepeatRate * 100).ToString("#0.00") }, JsonRequestBehavior.AllowGet);
        }

        public class SearchModel
        {
            public int visitorRate { get; set; }
            public decimal consumeMoney { get; set; }
            public int userCode { get; set; }
        }

        public ActionResult GetFlowBySql(string date1, string date2, string sort, string order)
        {
            //1.先查出  本月的交易成功的订单信息
            StringBuilder sql = new StringBuilder();
            //如果为空 所有的时间 搜索条件  就默认查询今天的记录
            if (string.IsNullOrEmpty(date1) || string.IsNullOrEmpty(date2))
            {
                sql.Append("SELECT SUM(orderSumprice+discountMoney) as consumeMoney,COUNT(*) as visitorRate ,member.memberBalance,member.memberExp,`user`.userCode,`user`.wechatName,`user`.wechatCode,userPhone from usersorder  LEFT JOIN `user` on `user`.userCode=usersorder.userCode LEFT JOIN member on member.wechatCode=`user`.wechatCode WHERE orderStatus=1 and DATE_FORMAT(orderTime, '%Y%m') = DATE_FORMAT(CURDATE( ) ,'%Y%m')   GROUP BY userCode ");
            }
            //如果日期  不为空
            if ((date1 != "" && date1 != null) && (date2 != "" && date2 != null))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("SELECT SUM(orderSumprice+discountMoney) as consumeMoney,COUNT(*) as visitorRate ,member.memberBalance,member.memberExp,`user`.userCode,`user`.wechatName,`user`.wechatCode,userPhone from usersorder  LEFT JOIN `user` on `user`.userCode=usersorder.userCode LEFT JOIN member on member.wechatCode=`user`.wechatCode WHERE orderStatus=1 and usersorder.orderTime BETWEEN '" + datetime1 + "'  and '" + datetime2 + "'   GROUP BY userCode ");
            }
            if (sort == "consumeMoney" && order == "desc")
            {
                sql.Append(" ORDER BY consumeMoney DESC  ");
            }
            if (sort == "consumeMoney" && order == "asc")
            {
                sql.Append(" ORDER BY consumeMoney ASC  ");
            }
            if (sort == "visitorRate" && order == "desc")
            {
                sql.Append(" ORDER BY visitorRate DESC  ");
            }
            if (sort == "visitorRate" && order == "asc")
            {
                sql.Append(" ORDER BY visitorRate ASC  ");
            }
            //var orderList2 = bll.Search(condition).GroupBy(x => x.userCode).Select(x => new { x.FirstOrDefault().userCode, visitorRate = x.Count(), consumeMoney = x.Sum(y => y.orderSumprice) }).ToList();
            var orderList2 = bll.SearchBySQL<ListFlow>(sql.ToString());
            //orderList2.ForEach(t => flowlist1.Add(new ListFlow
            //  {
            //      userCode = t.userCode,
            //      userPhone = t.userPhone,
            //      wechatName = t.wechatName,
            //      visitorRate = t.visitorRate,
            //      memberBalance = t.memberBalance,
            //      memberExp = t.memberExp

            //  }));
            return Json(new { rows = orderList2 }, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetOneOrderByUserBySql(string userCode, string date1, string date2)
        {
            int code;
            if (userCode != "null" && userCode != "")
            {
                code = Convert.ToInt32(userCode);
            }
            else
            {
                code = 0;
            }
            var oneOrderList = new List<oneOrder>();
            StringBuilder sql = new StringBuilder();
            if (string.IsNullOrEmpty(date1) || string.IsNullOrEmpty(date2))
            {
                //condition = x => x.orderStatus == 1 && x.userCode == code && x.orderTime.Month == DateTime.Now.Month;
                sql.Append("SELECT usersorder.* ,store.storeName,CASE orderPay WHEN 1 THEN '支付宝' when 2 THEN '微信' ELSE '人脸'  END AS orderType FROM usersorder INNER JOIN store on usersorder.storeid=store.storeid where orderStatus=1 and DATE_FORMAT(usersorder.orderTime, '%Y%m') = DATE_FORMAT(CURDATE( ) ,'%Y%m') and usersorder.userCode="+code+"");
            }
            //如果日期  不为空
            if ((date1 != "" && date1 != null) && (date2 != "" && date2 != null))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("SELECT usersorder.* ,store.storeName,CASE orderPay WHEN 1 THEN '支付宝' when 2 THEN '微信' ELSE '人脸'  END AS orderType FROM usersorder INNER JOIN store on usersorder.storeid=store.storeid where orderStatus=1 and usersorder.orderTime between '" + datetime1 + "' and '" + datetime2 + "'  and usersorder.userCode=" + code + "");
                //condition = x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2 && x.userCode == code;
            }
            //var list = bll.Search(condition).Select(x => new { x.storeid, x.orderCode, x.orderTime, x.orderSumprice, x.discountMoney, orderPay = x.orderPay == 1 ? "支付宝" : x.orderPay == 2 ? "微信" : x.orderPay == 3 ? "优惠券" : "人脸", });
            var list = bll.SearchBySQL<oneOrder>(sql.ToString());       
            //foreach (var item in list)
            //{
            //    string storeName = storeBll.Search(x => x.storeid == item.storeid).Select(x => new { x.storeName }).ToList().FirstOrDefault().storeName;
            //    oneOrder oneModel = new oneOrder();
            //    oneModel.discountMoney = item.discountMoney;
            //    oneModel.orderPay = item.orderPay;
            //    oneModel.orderSumprice = item.orderSumprice;
            //    oneModel.storeName = storeName;
            //    oneModel.orderCode = item.orderCode;
            //    oneModel.orderTime = item.orderTime.ToString("yyyy-MM-dd HH:mm:ss"); //ToShortTimeString()  ;//ToLongDateString ToLongTimeString
            //    oneOrderList.Add(oneModel);
            //}
            return Json(new { rows = list }, JsonRequestBehavior.AllowGet);
        }
    }
}




